package com.biz.hunter.dao;

import java.util.List;

import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.biz.hunter.db.entity.Hunter;
import com.biz.hunter.db.entity.HunterCompany;
import com.biz.hunter.dbutil.BaseDAO;

@Repository
public class HunterCompanyDAO {
	
	private static final String SQL_GET_ID = "select * from tb_huntercompany  where id = ?";
	private static final String SQL_GET_NAME = "select * from tb_huntercompany  where name = ?";
	private static final String SQL_GET_ALL = "select * from tb_huntercompany ";
	private static final String SQL_GET_ALL_CATEGORY = "select * from tb_huntercompany where domain =?";
	// TODO insert, update, delete
	private static final String SQL_SAVE = "insert into tb_huntercompany(name,orgnizationCode,licensecode,"
			+ "contact, email,phone,address,approved,createdate,imageUrl) values(?,?,?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE = "update tb_huntercompany set name=?,orgnizationCode=?,licensecode=?,"
			+ "contact=?, email=?,phone=?,address=?,approved=?,createdate=?,imageUrl =? where id =?";

	private static final String SQL_DELETE = "delete from tb_huntercompany where id=?";
	private static final String SQL_GET_ALL_COUNT = "select COUNT(*) from tb_huntercompany where 1=1 ";
	
	private static final String SQL_GET_ALL_WITH_LIMIT = "select * from tb_huntercompany where 1=1 ";

	public List<HunterCompany> getALL() {
		return BaseDAO.queryForList(HunterCompany.class, SQL_GET_ALL);
	}

	public List<HunterCompany> getDomainCompanies(int domainId) {
		return BaseDAO.queryForList(HunterCompany.class, SQL_GET_ALL_CATEGORY,
				domainId);
	}

	public HunterCompany getById(int Id) {
		return BaseDAO.queryForObject(HunterCompany.class, SQL_GET_ID, Id);
	}

	public HunterCompany getByName(String name) {
		return BaseDAO.queryForObject(HunterCompany.class, SQL_GET_NAME, name);
	}

	public int save(HunterCompany com) {
		if (com == null) {
			return -1;
		}
		Long result = BaseDAO.save(SQL_SAVE, com.getName(),
				com.getOrgnizationCode(), com.getLicenseCode(),
				com.getContact(), com.getEmail(), com.getPhone(),
				com.getAddress(), 
				com.getApproved(), com.getCreateDate(),com.getImageUrl());

		if (result == null) {
			return -1;
		}
		return result.intValue();

	}

	public int update(HunterCompany com) {
		if (com == null) {
			return -1;
		}
		int result = BaseDAO.execute(SQL_UPDATE, com.getName(),
				com.getOrgnizationCode(), com.getLicenseCode(),
				com.getContact(), com.getEmail(), com.getPhone(),
				com.getAddress(), 
				com.getApproved(), com.getCreateDate(),com.getImageUrl(),
				com.getId());
		return result;
	}

	public void deleteById(int Id) {
		int result = BaseDAO.execute(SQL_DELETE, Id);
	}
	
	public Long getCountOfAll(String name, String phoneNum,Integer status) {
		String sql = SQL_GET_ALL_COUNT;
		if (StringUtils.hasText(name)){
			sql+=" and name like '" + name +"'";
		}
		if (StringUtils.hasText(phoneNum)){
			sql+=" and phone ='" + phoneNum +"' ";
		}
		if(status!=null)
		{sql+= " and approved="+ status;}
		
		return (Long) BaseDAO.queryOneRowOneCol(sql);
	}
	
	public List<HunterCompany> getAllWithLimit(String name, String phoneNum, Integer status,long start, long length) {
		String sql = SQL_GET_ALL_WITH_LIMIT;
		if (StringUtils.hasText(name)){
			sql+=" and name like '" + name +"'";
		}
		if (StringUtils.hasText(phoneNum)){
			sql+=" and phone ='" + phoneNum +"' ";
		}
		if(status!=null)
		{sql+= "and approved= " + status;}
		sql+=" LIMIT ?, ?";
		return BaseDAO.queryForList(HunterCompany.class, sql, start, length);
	}
	
	public int delete(String Id) {

		if (Id != null) {

			int result = BaseDAO.execute(SQL_DELETE, Id);
			return result;
		} else {
			return 0;
		}
	}

}
